﻿<cfsilent>
	<cfscript>
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		
		examId = event.getArg("EXID");
		
		/* 加载考试任务 */
	
		sql = "SELECT b.term_start,
					   b.academic_year,
					   b.academic_term 
				FROM t_resittask a 
					 INNER JOIN t_term b ON b.term_id = a.term_id 
				WHERE a.task_exrstid = :examId ";
				
		queryObj = new Query( datasource=application.dnsSlave ) ;
   		queryObj.addParam( name="examId", value=examId, cfsqltype="cf_sql_char" );
		
		rs_term= queryObj.execute( sql=sql ).getResult();
		
		termstart = rs_term.term_start;
		
		sql = "SELECT b.course_name
				FROM t_resittask a 
					 INNER JOIN t_course b ON b.cid = a.cid 
				WHERE a.task_exrstid = :examId ";
				
		queryObj = new Query( datasource=application.dnsSlave ) ;
   		queryObj.addParam( name="examId", value=examId, cfsqltype="cf_sql_char" );
		
		rs_course= queryObj.execute( sql=sql ).getResult();
		
		downFile = URLEncodedFormat( rs_term.academic_year & "-" & rs_term.academic_term & "," & rs_course.course_name & "补考安排", "utf-8");
		
		/* 载入相关操作组件 */
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		mathAdvice = getProperty("serviceFactory").getBean("senateMathAdvice");
		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		dictAdvice = getProperty("serviceFactory").getBean("senateDictionaryAdvice");
		
		/* 生成电子表格数据 */
	
											
		sql = "SELECT 
				d.institute_name,c.course_name, 
				c.course_credit, 
				case when c.course_code is null then '-' else c.course_code end course_code, 
				b.task_exrstid,b.wid, 
				b.wdidx,e.cellidx, 
				e.time_start, e.time_end, 
				f.classroom, f.cr_floor, 
				g.b_name,count(*) count1
				FROM t_student_resit a  
					 INNER JOIN t_resittask b ON b.task_exrstid = a.task_exrstid 
					 INNER JOIN t_course c ON c.cid = b.cid 
					 INNER JOIN t_institute d ON d.institute_id = c.institute_id 
					 INNER JOIN t_examcell e ON e.cellidx = b.cellidx 
					 INNER JOIN t_Classroom f ON f.cr_id = a.cr_id 
					 INNER JOIN t_building g ON g.b_id = f.b_id 
				WHERE 
					b.task_exrstid = :examId 
					AND 
					b.task_prog = '3' 
				GROUP BY 
					d.institute_name,c.course_name, 
					c.course_credit,course_code, 
					b.task_exrstid,b.wid, 
					b.wdidx, e.cellidx, 
					e.time_start, e.time_end, 
					f.classroom, f.cr_floor, 
					g.b_name 
				ORDER BY 
          			g.b_name, 
					f.cr_floor,
          			f.classroom ";
					
		queryObj = new Query( datasource=application.dnsSlave ) ;
		queryObj.addParam( name="examId", value=examId, cfsqltype="cf_sql_char" );
		
		rs_exam= queryObj.execute( sql=sql ).getResult();
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("考场分布情况", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "任课单位,课程,学分,课程号,考试编号,考试时间,考试周次,场次安排,起止时间,考场,教学楼,所在楼层,参考人数");
		
		row = 1;
		
		/* 写入考场安排 */
		for (  a=1; a LTE rs_exam.recordCount; a++ ) {
			
			row++;
			
			rowData = rs_exam["institute_name"][a]
						& "," & rs_exam["course_name"][a] 
						& "," & numberFormat( rs_exam["course_credit"][a], "_.__" ) 
						& "," & rs_exam["course_code"][a] 
						& "," & rs_exam["task_exrstid"][a] 
						& "," & datetimeAdvice.formatDateString( mathAdvice.revertTaskWeekDay( termstart, rs_exam["wid"][a], rs_exam["wdidx"][a] ), "YYYY年MM月DD日") & " (" & datetimeAdvice.formatTimeString( rs_exam["time_start"][a], "HH:MI" ) & "~" & datetimeAdvice.formatTimeString( rs_exam["time_end"][a], "HH:MI" ) & ")"
						& "," & rs_exam["wid"][a] 
						& "," & mathAdvice.convertWeekDayToChinese( rs_exam["wdidx"][a] ) & mathAdvice.convertExamIdxToChinese( rs_exam["cellidx"][a] ) 
						& "," & datetimeAdvice.formatTimeString( rs_exam["time_start"][a], "HH:MI" ) & "~" & datetimeAdvice.formatTimeString( rs_exam["time_end"][a], "HH:MI" )
						& "," & rs_exam["classroom"][a] 
						& "," & rs_exam["b_name"][a] 
						& "," & dictAdvice.getFloorString( rs_exam["cr_floor"][a] ) 
						& "," & rs_exam["count1"][a];
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData); 
			
		}
		
		/* 创建学生详细名单 */
		excel.SpreadsheetCreateSheet (spreadsheetObj, "学生名单");
		excel.SpreadsheetSetActiveSheet (spreadsheetObj, "学生名单");
									
		sql = "SELECT 
				c.b_name,b.classroom,
				a.sitidx,a.stu_id, 
				d.stu_name, d.stu_sex, 
				e.cls_name, e.grade, 
				i.sbj_short,j.institute_short
				FROM t_student_resit a 
				   INNER JOIN t_classroom b ON b.cr_id = a.cr_id 
				   INNER JOIN t_building c ON c.b_id = b.b_id 
				   INNER JOIN t_student d ON d.stu_id = a.stu_id 
				   INNER JOIN t_class e ON e.cls_id = d.cls_id 
				   INNER JOIN t_resittask f ON f.task_exrstid = a.task_exrstid 
				   INNER JOIN t_course g ON g.cid = f.cid 
				   INNER JOIN t_examcell h ON h.cellidx = f.cellidx 
				   INNER JOIN t_subject i ON i.sbj_id = e.sbj_id 
				   INNER JOIN t_institute j ON j.institute_id = i.institute_id  
				WHERE 
					a.task_exrstid = :examId 
				ORDER BY 
					c.b_name, 
					b.classroom, 
					a.sitidx";
					
		queryObj = new Query( datasource=application.dnsSlave ) ;
		queryObj.addParam( name="examId", value=examId, cfsqltype="cf_sql_char" );
		
		rs_student= queryObj.execute( sql=sql ).getResult();
		
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "教学楼,考场,座次,学号,姓名,性别,班级,年级,专业,学院");
		
		row = 1;
		
		for (  b=1; b LTE rs_student.recordCount; b++  ) {
			
			row++;
			
			rowData = rs_student["b_name"][b]
						& "," & rs_student["classroom"][b] 
						& "," & rs_student["sitidx"][b] 
						& "," & rs_student["stu_id"][b] 
						& "," & rs_student["stu_name"][b];
			
			if ( rs_student["stu_sex"][b] eq "1" ) {
				rowData = rowData & ",男";
			}
			else {
				rowData = rowData & ",女";
			}
			 
			rowData = rowData
						& "," & rs_student["cls_name"][b] 
						& "," & rs_student["grade"][b] 
						& "," & rs_student["sbj_short"][b] 
						& "," & rs_student["institute_short"][b]; 
			
			/* 写入学生信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
				
		}
	
		excel.SpreadsheetSetActiveSheet (spreadsheetObj, "考场分布情况");
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
	
	</cfscript>
	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />
</cfsilent>
